package efrei.ngo.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.GregorianCalendar;

import efrei.ngo.entity.Floor;
import efrei.ngo.entity.ItemDetail;
import efrei.ngo.entity.ItemSimple;
import efrei.ngo.entity.MsgPiece;
import efrei.ngo.entity.ResourcePiece;
import efrei.ngo.entity.User;

public class DatabaseAccess {

	static String driver = "com.mysql.jdbc.Driver";

	// URL indicate which database DBSansFrontiere
	static String url = "jdbc:mysql://127.0.0.1:3306/DBSansFrontiere";

	// MySQL UserName
	static String user = "root";

	// Java---MySQL Password
	static String password = null;

	// For the connection
	static Connection conn;

	// control bit for the static DB
	static boolean valid = false;

	/*
	 * function used to connect the database
	 */
	static public void connectToDB() {
		try {
			// Load in driver
			Class.forName(driver);

			// Connecting DB
			conn = DriverManager.getConnection(url, user, password);

			if (!conn.isClosed()) {
				//System.out.println("Succeeded connecting to the Database!");
				valid=true;
			} else {
				//System.out.println("Failed connecting to the Database!");
				valid=false;
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	/*
	 * The function used to end the connection between the database
	 */
	static public boolean endConnectionToDB(){
		try{
			if(valid){
				//conn.close();
				valid=false;
				//System.out.println("Disconnect to the DB");
				return true;
			}else{
				//System.out.println("Fail to disconnect to the DB");
				return false;
			}
		}catch(Exception ex){
			ex.printStackTrace();
			return false;
		}
	}

	public static boolean dbLoginViaUsername(String username, String psw,
			User requestOne) {
		try{
			Statement statement = conn.createStatement();
			String sql = "SELECT *FROM PersonalInformation"
				+" WHERE usename='"
				+username+"'AND psw='"
				+psw+"'";
			ResultSet rs=statement.executeQuery(sql);
			if(rs.next()){
				requestOne.setU_id(rs.getInt("id_pi"));
				requestOne.setUsername(rs.getString("usename"));
				requestOne.setEmail(rs.getString("email"));
				requestOne.setAuth(rs.getString("auth").charAt(0));
				statement.close();
				return true;
			}else{
				statement.close();
				return false;
			}
		}catch(Exception ex){
			ex.printStackTrace();
			return false;
		}
	}

	public static boolean dbLoginViaEmail(String email, String psw,
			User requestOne) {
		try{
			Statement statement = conn.createStatement();
			String sql = "SELECT *FROM PersonalInformation"
				+" WHERE email='"
				+email+"'AND psw='"
				+psw+"'";
			ResultSet rs=statement.executeQuery(sql);
			if(rs.next()){
				requestOne.setUsername(rs.getString("usename"));
				requestOne.setEmail(rs.getString("email"));
				requestOne.setAuth(rs.getString("auth").charAt(0));
				statement.close();
				return true;
			}else{
				statement.close();
				return false;
			}
		}catch(Exception ex){
			ex.printStackTrace();
			return false;
		}
	}

	public static boolean dbCheckUsernameUni(String username) {
		try{
			Statement statement = conn.createStatement();
			String sql = "SELECT *FROM PersonalInformation"
				+" WHERE usename='"
				+username+"'";
			if(statement.executeQuery(sql).next()){
				statement.close();
				return false;
			}else{
				statement.close();
				return true;
			}
		}catch(Exception ex){
			ex.printStackTrace();
			return false;
		}
	}

	public static boolean dbCheckEmailUni(String email) {
		try{
			Statement statement = conn.createStatement();
			String sql = "SELECT *FROM PersonalInformation"
				+" WHERE email='"
				+email+"'";
			if(statement.executeQuery(sql).next()){
				statement.close();
				return false;
			}else{
				statement.close();
				return true;
			}
		}catch(Exception ex){
			ex.printStackTrace();
			return false;
		}
	}

	/*
	 * Store personal information into DB
	 */
	public static boolean dbStorePIIntoDB(String email, String username,
			String psw, String sex, String career, String bday,
			boolean isInternal) {
		try{
			Statement statement = conn.createStatement();
			String sql = "INSERT INTO PersonalInformation"
				+"(email,usename,psw,sex,career,bday,isInternal)"
				+" VALUES ('"
				+email+"','"
				+username+"','"
				+psw+"','"
				+sex+"','"
				+career+"',"
				+bday+","
				+ isInternal +")";
			if(statement.executeUpdate(sql)==1){
				//conn.commit();
				statement.close();
				return true;
			}else{
				//conn.commit();
				statement.close();
				return false;
			}
		}catch(Exception ex){
			ex.printStackTrace();
			return false;
		}
	}

	/*
	 * get the list of the whole post, SimpleItem
	 */
	public static ArrayList<ItemSimple> dbGetPostList() {
		ArrayList<ItemSimple> itemList = new ArrayList<ItemSimple>();
		try{
			Statement statement = conn.createStatement();
			String sql = "SELECT postID,titleName,description,postDate,useName FROM forumitemlist,PersonalInformation WHERE forumitemlist.owner=PersonalInformation.id_pi";
			ResultSet rs=statement.executeQuery(sql);
			
			while(rs.next()){
				itemList.add(new ItemSimple(rs.getString("postID"), rs.getString("titleName"), rs.getString("description"),
						efrei.ngo.util.GeneralUnil.changeDateToGregorianCalendar(rs.getDate("postDate")), rs.getString("useName")));
			}
			statement.close();
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return itemList;
	}

	/*
	 * store a single post into the Database
	 */
	public static boolean dbStorePostInDB(String titleName,String description,String context,int owner){
		try{
			Statement statement = conn.createStatement();
			String sql = "INSERT INTO forumitemlist"
				+"(titleName,description,postDate,owner,content)"
				+" VALUES ('"
				+titleName+"','"
				+description+"','"
				+new java.sql.Date(new Date().getTime())+"',"
				+owner+",'"
				+context
				+"')";
			if(statement.executeUpdate(sql)==1){
				//conn.commit();
				statement.close();
				return true;
			}else{
				//conn.commit();
				statement.close();
				return false;
			}
		}catch(Exception ex){
			ex.printStackTrace();
			return false;
		}
	}
	
	/*
	 * return a single detail of the post; content included.
	 */
	public static ItemDetail dbGetPostD(String postID) {
		ItemDetail result=null;

		try{
			Statement statement = conn.createStatement();
			String sql = "SELECT postID,titleName,description,postDate,useName,content FROM forumitemlist,PersonalInformation WHERE forumitemlist.owner=PersonalInformation.id_pi AND postID="+postID;
			ResultSet rs=statement.executeQuery(sql);
			
			if(rs.next()){
				result = new ItemDetail(rs.getString("postID"), rs.getString("titleName"), rs.getString("description"),
						efrei.ngo.util.GeneralUnil.changeDateToGregorianCalendar(rs.getDate("postDate")), rs.getString("useName"),rs.getString("content"));
			}
			statement.close();
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return result;
	}

	/*
	 * get a list of floor according to a give id of the post
	 */
	public static ArrayList<Floor> dbGetFloorList(String postID) {
		ArrayList<Floor> floorList = new ArrayList<Floor>();
		
		try{
			Statement statement = conn.createStatement();
			String sql = "SELECT * FROM PostFloorList WHERE postID="+postID;
			ResultSet rs=statement.executeQuery(sql);
			Floor temp4add;
			
			while(rs.next()){
				temp4add=new Floor(rs.getString("postID"), rs.getString("floorID"), rs.getString("context"),
						efrei.ngo.util.GeneralUnil.changeDateToGregorianCalendar(rs.getDate("postDate")), new User(rs.getInt("owner")),new User(rs.getInt("toSomebody")));
				floorList.add(temp4add);
			}
			statement.close();
		}catch(Exception ex){
			ex.printStackTrace();
		}
		
		return floorList;
	}
	
	/*
	 * Store a floor into DB
	 */
	public static boolean dbStoreFoorIntoDB(int postID,String context,GregorianCalendar postDate,int owner,int toSomebody){
		try{
			Statement statement=conn.createStatement();
			String sql = "INSERT INTO PostFloorList"
					+"(postID,context,postDate,owner,toSomebody)"
					+" VALUES ("
					+postID+",'"
					+context+"','"
					+new java.sql.Date(new Date().getTime())+"',"
					+owner+","
					+toSomebody
					+")";
			if(statement.executeUpdate(sql)==1){
				//conn.commit();
				statement.close();
				return true;
			}else{
				//conn.commit();
				statement.close();
				return false;
			}
		}catch(Exception ex){
			ex.printStackTrace();
			return false;
		}
	}
	
	/*
	 * to get a corresponding name to a id
	 */
	public static String dbGetNameViaID(int id_pi){
		try{
			Statement statement=conn.createStatement();
			String sql ="SELECT usename FROM PersonalInformation WHERE id_pi="+id_pi;
			ResultSet rs=statement.executeQuery(sql);
			
			if(rs.next()){
				return rs.getString("usename");
			}else{
				return null;
			}
		}catch(Exception ex){
			ex.printStackTrace();
			return null;
		}
	}
	
	/*
	 * to get the id from the name
	 */
	public static int dbGetIDViaName(String name){
		try{
			Statement statement=conn.createStatement();
			String sql ="SELECT id_pi FROM PersonalInformation WHERE usename='"+name+"'";
			ResultSet rs=statement.executeQuery(sql);
			
			if(rs.next()){
				return rs.getInt("id_pi");
			}else{
				return -1;
			}
		}catch(Exception ex){
			ex.printStackTrace();
			return -1;
		}
	}

	public static ArrayList<MsgPiece> dbCheckNewIM(){
		ArrayList<MsgPiece> list=new ArrayList<MsgPiece>();
		try{
			Statement statement=conn.createStatement();
			String sql ="SELECT * FROM im WHERE isRead='f'";
			ResultSet rs=statement.executeQuery(sql);
			MsgPiece onePiece;
			
			while(rs.next()){
				onePiece=new MsgPiece(rs.getInt("id_im"), rs.getInt("pFrom"), rs.getInt("pTo"), rs.getString("content"), efrei.ngo.util.GeneralUnil.changeDateToGregorianCalendar(rs.getTime("sTime")));
				list.add(onePiece);
				dbUpdateIsReadMark(onePiece.getId_im());
			}
		}catch(Exception ex){
			ex.printStackTrace();
			return null;
		}
		return list;
	}
	
	/*
	 * Mark the record isRead
	 */
	private static boolean dbUpdateIsReadMark(int id_im){
		try{
			Statement statement=conn.createStatement();
			String sql ="UPDATE im SET isRead='t' WHERE id_im="+id_im;
			if(statement.executeUpdate(sql)==1){
				return true;
			}else{
				return false;
			}
		}catch(Exception ex){
			ex.printStackTrace();
			return false;
		}
	}
	
	/*
	 * Store a piece of im into DB
	 */
	public static boolean dbStoreIMIntoDB(MsgPiece onePiece) {
		try{
			Statement statement = conn.createStatement();
			String sql = "INSERT INTO im"
				+"(pFrom,pTo,content,sTime,isRead)"
				+" VALUES (" +onePiece.getpFrom()+
				"," +onePiece.getpTo()+
				",'"+onePiece.getContent()+
				"','" +new java.sql.Date(new Date().getTime())+
				"','f'" +
				")";
			if(statement.executeUpdate(sql)==1){
				//conn.commit();
				statement.close();
				return true;
			}else{
				//conn.commit();
				statement.close();
				return false;
			}
		}catch(Exception ex){
			ex.printStackTrace();
			return false;
		}
	}

	public static ArrayList<String> dbGetCountryListViaContinent(String continent){
		ArrayList<String> arrCountry=new ArrayList<String>();
		
		try{
			Statement statement=conn.createStatement();
			String sql ="SELECT DISTINCT country FROM rsLocation WHERE continent='"+continent+"'";
			ResultSet rs=statement.executeQuery(sql);
			
			while(rs.next()){
				arrCountry.add(rs.getString("country"));
			}
			statement.close();
		}catch(Exception ex){
			ex.printStackTrace();
			return null;
		}
		return arrCountry;
	}
	
	/*
	 * Insert record into DB
	 */
	public static boolean dbInsertResourceRecord(int id_rs,int id_pi,double quantity){
		try{
			Statement statement2 = conn.createStatement();
			String sql2 = "INSERT INTO rsRecord"
				+"(date,id_rs,id_pi,quantity)"
				+" VALUES ('"+new java.sql.Date(new Date().getTime())+"',"+id_rs+","+id_pi+","+quantity+")";
			if(statement2.executeUpdate(sql2)==1){
				//conn.commit();
				statement2.close();
				return true;
			}else{
				//conn.commit();
				statement2.close();
				return false;
			}
		}catch(Exception ex){
			ex.printStackTrace();
			return false;
		}
	}
	
	/*
	 * Update the resource
	 */
	public static boolean dbUpdateResource(int id_rs,double quantity, int id_pi){
		try{
			Statement statement=conn.createStatement();
			String sql ="UPDATE rslist SET quantity=quantity+"+quantity+" WHERE id_rs="+id_rs;
			if(statement.executeUpdate(sql)==1){
				statement.close();
				return DatabaseAccess.dbInsertResourceRecord(id_rs, id_pi, quantity);
			}else{
				statement.close();
				return false;
			}
		}catch(Exception ex){
			ex.printStackTrace();
			return false;
		}
	}

	public static boolean dbInsertResource(String diagName,String diagType,String diaglocation,String diagCity,double diagQuan,String diagUnit,String diagDate, int id_pi){
		try{
			int id_type=DatabaseAccess.dbGetResourceType(diagType, diagName);
			int id_location=DatabaseAccess.dbGetResourceLocation(diaglocation, diagCity);
			
			Statement statement=conn.createStatement();
			String sql = "INSERT INTO rsList"
					+"(id_type,id_location,quantity,unit,lu_date)"
					+" VALUES ("+id_type+","+id_location+","+diagQuan+",'"+diagUnit+"','"+new java.sql.Date(new Date(diagDate).getTime())+"')";
			if(statement.executeUpdate(sql)==1){
				//conn.commit();
				statement.close();
				int id_rs=DatabaseAccess.dbGetInsertResourceID(id_type, id_location, diagQuan, diagUnit, new java.sql.Date(new Date(diagDate).getTime()));
				return DatabaseAccess.dbInsertResourceRecord(id_rs, id_pi, diagQuan);
			}else{
				//conn.commit();
				statement.close();
				return false;
			}
		}catch(Exception ex){
			ex.printStackTrace();
			return false;
		}
	}

	private static int dbGetInsertResourceID(int id_type,int id_location,double quantity,String unit,java.sql.Date lu_date){
		try{
			Statement statement=conn.createStatement();
			String sql = "SELECT id_rs FROM rslist WHERE id_type="+id_type+" AND id_location="+id_location+" AND quantity="+quantity+" AND unit='"+unit+"' AND lu_date='"+lu_date+"'";
			ResultSet rs=statement.executeQuery(sql);
			if(rs.next()){
				int temp=rs.getInt("id_rs");
				statement.close();
				return temp;
			}else{
				statement.close();
				return -1;
			}
		}catch(Exception ex){
			ex.printStackTrace();
			return -1;
		}
	}
	
	/*
	 * get the id of the location and city group OR insert a new one
	 */
	public static int dbGetResourceLocation(String diaglocation, String diagCity){
		try{
			Statement statement=conn.createStatement();
			String sql = "SELECT id_location FROM rsLocation WHERE country='"+diaglocation+"' AND city='"+diagCity+"'";
			ResultSet rs=statement.executeQuery(sql);
			if(rs.next()){
				int temp=rs.getInt("id_location");
				statement.close();
				return temp;
			}else{
				sql = "SELECT continent FROM rsLocation WHERE country='"+diaglocation+"'";
				rs=statement.executeQuery(sql);
				String continent=null;
				if(rs.next()){
					continent=rs.getString("continent");
				}
				
				sql = "INSERT INTO rsLocation (continent, country, city) VALUES ('"+continent+"','"+diaglocation+"','"+diagCity+"')";
				if(statement.executeUpdate(sql)==1){
					statement.close();
					return dbGetResourceLocation(diaglocation,diagCity);
				}else{
					statement.close();
					return -1;
				}
			}
		}catch(Exception ex){
			ex.printStackTrace();
			return -1;
		}
	}
	
	/*
	 * Get the id of the type name couple OR insert a new one
	 */
	public static int dbGetResourceType(String diagType, String diagName){
		try{
			Statement statement=conn.createStatement();
			String sql = "SELECT id_type FROM rstypelist WHERE type='"+diagType+"' AND name='"+diagName+"'";
			ResultSet rs=statement.executeQuery(sql);
			if(rs.next()){
				int temp=rs.getInt("id_type");
				statement.close();
				return temp;
			}else{
				sql = "INSERT INTO rstypelist (type, name) VALUES ('"+diagType+"','"+diagName+"')";
				if(statement.executeUpdate(sql)==1){
					statement.close();
					return dbGetResourceType(diagType, diagName);
				}else{
					statement.close();
					return -1;
				}
			}
		}catch(Exception ex){
			ex.printStackTrace();
			return -1;
		}
	}

	public static ArrayList<ResourcePiece> dbGetRSList(String sql){
		ArrayList<ResourcePiece> list=new ArrayList<ResourcePiece>();
		try{
			Statement statement=conn.createStatement();
			ResultSet rs=statement.executeQuery(sql);
			ResourcePiece oneRS;
			
			while(rs.next()){
				oneRS=new ResourcePiece(rs.getInt("id_rs"), rs.getString("name"), rs.getString("type"), rs.getString("country"),
			rs.getString("city"), rs.getDouble("quantity"), rs.getString("unit"), rs.getDate("lu_date"));
				list.add(oneRS);
			}
			statement.close();
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return list;
	}
	
	public static ArrayList<String> dbGetTypeList(){
		ArrayList<String> arrType=new ArrayList<String>();
		
		try{
			Statement statement=conn.createStatement();
			String sql ="SELECT DISTINCT type FROM rstypelist";
			ResultSet rs=statement.executeQuery(sql);
			
			while(rs.next()){
				arrType.add(rs.getString("type"));
			}
			statement.close();
		}catch(Exception ex){
			ex.printStackTrace();
			return null;
		}
		return arrType;
	}
	
	public static ArrayList<String> dbGetContinentList(){
		ArrayList<String> arrType=new ArrayList<String>();
		
		try{
			Statement statement=conn.createStatement();
			String sql ="SELECT DISTINCT continent FROM rslocation";
			ResultSet rs=statement.executeQuery(sql);
			
			while(rs.next()){
				arrType.add(rs.getString("continent"));
			}
			statement.close();
		}catch(Exception ex){
			ex.printStackTrace();
			return null;
		}
		return arrType;
	}
	
	public static ArrayList<User> dbGetFriendList(){
		ArrayList<User> arrUser=new ArrayList<User>();
		
		try{
			Statement statement=conn.createStatement();
			String sql ="SELECT usename,id_pi FROM PersonalInformation";
			ResultSet rs=statement.executeQuery(sql);
			
			while(rs.next()){
				arrUser.add(new User(rs.getInt("id_pi"),rs.getString("usename")));
			}
			statement.close();
		}catch(Exception ex){
			ex.printStackTrace();
			return null;
		}
		return arrUser;
	}
}